﻿using System.Data.SqlClient;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace WebAPI存储过程多接口聚合.SQLHelper
{
    public class OracleHelper
    {
        private readonly IConfiguration _configuration;
        public OracleHelper(IConfiguration connectionString)
        {
            _configuration = connectionString;
        }

        public void ExecuteNonQuery(string storedProcedureName, string json, string dataKey)
        {
            string connString = _configuration.GetConnectionString(dataKey);
            OracleParameter parameters = new OracleParameter("@UserData", SqlDbType.NVarChar)
            {
                Value = json
            };
            using (OracleConnection connection = new OracleConnection(connString))
            {
                using (OracleCommand command = new OracleCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                    {
                        command.Parameters.Add(parameters);
                    }

                    connection.Open();

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        public DataTable ExecuteQuery(string storedProcedureName, string json, string dataKey)
        {
            string connString = _configuration.GetConnectionString(dataKey);
            DataTable dataTable = new DataTable();
            OracleParameter parameters = new OracleParameter("@UserData", SqlDbType.NVarChar)
            {
                Value = json
            };
            using (OracleConnection connection = new OracleConnection(connString))
            {
                using (OracleCommand command = new OracleCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                    {
                        command.Parameters.Add(parameters);
                    }

                    using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                    {
                        try
                        {
                            adapter.Fill(dataTable);
                            return dataTable;
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }

                    }
                }
            }
        }
    }
}
